package org.chen.scene.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;
import org.chen.common.scene.dto.AttractionResponseDTO;
import org.chen.common.scene.entity.Attraction;

import java.math.BigDecimal;
import java.util.List;

@Mapper
public interface AttractionMapper extends BaseMapper<Attraction> {

    @Select({
            "<script>",
            "SELECT a.*, GROUP_CONCAT(t.tag_name) as tag_names ",
            "FROM scene_attractions a ",
            "LEFT JOIN scene_attraction_tags t ON a.poi_id = t.poi_id ",
            "WHERE 1=1 ",
            "<if test='cityName != null and cityName != \"\"'>",
            "  AND a.district_name = #{cityName} ",
            "</if>",
            "<if test='name != null and name != \"\"'>",
            "  AND a.poi_name LIKE CONCAT('%', #{name}, '%') ",
            "</if>",
            "GROUP BY a.id ",
            "ORDER BY ",
            "CASE WHEN #{sortType} = 9 THEN a.heat_score END DESC, ",
            "CASE WHEN #{sortType} = 4 THEN a.comment_score END DESC, ",
            "CASE WHEN #{sortType} = 1 THEN a.comment_count END DESC ",
            "LIMIT #{offset}, #{size}",
            "</script>"
    })
    @Results({
            @Result(column = "tag_names", property = "tagNames", typeHandler = StringToListTypeHandler.class)
    })
    List<AttractionResponseDTO> selectWithTags(@Param("cityName") String cityName,
                                               @Param("name") String name,
                                               @Param("sortType") Integer sortType,
                                               @Param("offset") Integer offset,
                                               @Param("size") Integer size);

    /**
     * 根据经纬度和半径查询附近景点
     * 使用Haversine公式计算距离
     */
    @Select("<script>" +
            "SELECT a.*, " +
            "GROUP_CONCAT(t.tag_name) as tag_names, " +
            "(" +
            "  6371 * acos(" +
            "    cos(radians(#{latitude})) * cos(radians(a.latitude)) * " +
            "    cos(radians(a.longitude) - radians(#{longitude})) + " +
            "    sin(radians(#{latitude})) * sin(radians(a.latitude))" +
            "  )" +
            ") AS distance_km " +
            "FROM scene_attractions a " +
            "LEFT JOIN scene_attraction_tags t ON a.poi_id = t.poi_id " +
            "WHERE a.latitude IS NOT NULL " +
            "AND a.longitude IS NOT NULL " +
            "AND (" +
            "  6371 * acos(" +
            "    cos(radians(#{latitude})) * cos(radians(a.latitude)) * " +
            "    cos(radians(a.longitude) - radians(#{longitude})) + " +
            "    sin(radians(#{latitude})) * sin(radians(a.latitude))" +
            "  )" +
            ") &lt;= #{radiusKm} " +
            "<if test='minScore != null'>" +
            "AND a.comment_score &gt;= #{minScore} " +
            "</if>" +
            "<if test='freeOnly != null and freeOnly == true'>" +
            "AND a.is_free = true " +
            "</if>" +
            "<if test='sightLevel != null and sightLevel != \"\"'>" +
            "AND a.sight_level = #{sightLevel} " +
            "</if>" +
            "GROUP BY a.id " +
            "ORDER BY " +
            "CASE WHEN #{sortType} = 1 THEN CAST(a.heat_score AS DECIMAL(10,2)) END DESC, " +
            "CASE WHEN #{sortType} = 2 THEN distance_km END ASC, " +
            "CASE WHEN #{sortType} = 4 THEN a.comment_score END DESC " +
            "LIMIT #{offset}, #{size}" +
            "</script>")
    List<AttractionResponseDTO> selectNearbyAttractions(@Param("latitude") BigDecimal latitude,
                                                        @Param("longitude") BigDecimal longitude,
                                                        @Param("radiusKm") Integer radiusKm,
                                                        @Param("sortType") Integer sortType,
                                                        @Param("offset") Integer offset,
                                                        @Param("size") Integer size,
                                                        @Param("minScore") BigDecimal minScore,
                                                        @Param("freeOnly") Boolean freeOnly,
                                                        @Param("sightLevel") String sightLevel);

    /**
     * 统计附近景点总数
     */
    @Select("<script>" +
            "SELECT COUNT(*) " +
            "FROM scene_attractions a " +
            "WHERE a.latitude IS NOT NULL " +
            "AND a.longitude IS NOT NULL " +
            "AND (" +
            "  6371 * acos(" +
            "    cos(radians(#{latitude})) * cos(radians(a.latitude)) * " +
            "    cos(radians(a.longitude) - radians(#{longitude})) + " +
            "    sin(radians(#{latitude})) * sin(radians(a.latitude))" +
            "  )" +
            ") &lt;= #{radiusKm} " +
            "<if test='minScore != null'>" +
            "AND a.comment_score &gt;= #{minScore} " +
            "</if>" +
            "<if test='freeOnly != null and freeOnly == true'>" +
            "AND a.is_free = true " +
            "</if>" +
            "<if test='sightLevel != null and sightLevel != \"\"'>" +
            "AND a.sight_level = #{sightLevel} " +
            "</if>" +
            "</script>")
    Long countNearbyAttractions(@Param("latitude") BigDecimal latitude,
                                @Param("longitude") BigDecimal longitude,
                                @Param("radiusKm") Integer radiusKm,
                                @Param("minScore") BigDecimal minScore,
                                @Param("freeOnly") Boolean freeOnly,
                                @Param("sightLevel") String sightLevel);
}